﻿using QuanLyCongDan.DTO;
using System.Data.Common;
using System.Data;
using System;
using System.Collections.Generic;

namespace DAL
{
    public class DAL_TaiKhoan
    {
        /// <summary>
        /// Insert this object TaiKhoan into database.
        /// </summary>
        /// <param name="taiKhoanInput">The inserting TaiKhoan object</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertTaiKhoan(DTO_TaiKhoan taiKhoanInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "INSERT INTO TaiKhoan " +
                "([Username], [Password]) " +
                "VALUES " +
                "(@Username, @Password)";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Username";
            param.Value = string.IsNullOrEmpty(taiKhoanInput.Username) ? string.Empty : taiKhoanInput.Username;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Password";
            param.Value = string.IsNullOrEmpty(taiKhoanInput.Password) ? string.Empty : taiKhoanInput.Password;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Update this object TaiKhoan from database.
        /// </summary>
        /// <param name="taiKhoanInput">The updating TaiKhoan object</param>
        /// <returns>True if update successfully, false if not</returns>
        public static bool UpdateTaiKhoan(DTO_TaiKhoan taiKhoanInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "UPDATE TaiKhoan " +
                "SET [Password] = @Password" +
                "WHERE [Username] = @Username";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Username";
            param.Value = string.IsNullOrEmpty(taiKhoanInput.Username) ? string.Empty : taiKhoanInput.Username;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Password";
            param.Value = string.IsNullOrEmpty(taiKhoanInput.Password) ? string.Empty : taiKhoanInput.Password;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Delete this object TaiKhoan from database.
        /// </summary>
        /// <param name="taiKhoanInput">The deleting TaiKhoan object</param>
        /// <returns>True if delete successfully, false if not</returns>
        public static bool DeleteTaiKhoan(DTO_TaiKhoan taiKhoanInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            comm.CommandText =
                "DELETE TaiKhoan " +
                "WHERE [Password] = @Password";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Password";
            param.Value = string.IsNullOrEmpty(taiKhoanInput.Password) ? string.Empty : taiKhoanInput.Password;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Retrieves all TaiKhoan in database.
        /// </summary>
        /// <returns>List of all TaiKhoan.</returns>
        public static List<DTO_TaiKhoan> GetListTaiKhoan()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT [Username] FROM TaiKhoan";

            List<DTO_TaiKhoan> taiKhoanList = new List<DTO_TaiKhoan>();
            // execute the command
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_TaiKhoan curTaiKhoan = new DTO_TaiKhoan();
                curTaiKhoan.Username = (dr["Username"] == System.DBNull.Value) ? string.Empty : (string)dr["Username"];
                taiKhoanList.Add(curTaiKhoan);
            }
            return taiKhoanList;
        }

        /// <summary>
        /// Retrieves TaiKhoan's password by its username in database.
        /// </summary>
        /// <returns>Password of a specific TaiKhoan.</returns>
        public static string GetPassword(string Username)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT [Password] FROM TaiKhoan WHERE Username = @Username";
            
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Username";
            param.Value = string.IsNullOrEmpty(Username) ? string.Empty : Username;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);
            
            // execute the command
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            DataRow dr = dt.Rows[0];
            string password = (dr["Password"] == System.DBNull.Value) ? string.Empty : (string)dr["Password"];
            return password;
        }

        /// <summary>
        /// Check if TaiKhoan's username is already exists in database.
        /// </summary>
        /// <returns>True if username exists, false if not.</returns>
        public static bool IsUsernameExist(string Username)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT Count(*) FROM TaiKhoan WHERE Username = @Username";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Username";
            param.Value = string.IsNullOrEmpty(Username) ? string.Empty : Username;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            return GenericDataAccess.ExecuteScalarQuery(comm) > 0;
        }


        /// <summary>
        /// Update TaiKhoan table
        /// </summary>
        public static void UpdateTaiKhoanTable(DataSet ds)
        {
            System.Data.OleDb.OleDbConnection cn = GenericDataAccess.GetConnection();
            string strSQL = "Select [Username] From TaiKhoan";
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
            System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
            da.Update(ds.Tables[0]);
            cn.Close();
        }

        /// <summary>
        /// Get TaiKhoan table
        /// </summary>
        /// <returns>The dataset of TaiKhoan table</returns>
        public static DataSet GetTaiKhoanTable()
        {
            System.Data.OleDb.OleDbConnection cn = GenericDataAccess.GetConnection();
            string strSQL = "Select [Username] From TaiKhoan";
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);
            da.Fill(ds);
            cn.Close();
            return ds;
        }
    }
}
